﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Threading;
using Wise.Entities;
using Wise.Util;

namespace NHI.Sync.Tools
{
    public static class MyDbExtension
    {
        public static List<string> GetTables(this 数据库配置 Data)
        {
            return CacheHelper.Get<List<string>, 数据库配置>(
                $"{Data.服务器名称}.{Data.用户名}.GetTables"
                , _GetTabls, Data);

        }
        public static List<string> GetDatasources(this 数据库配置 Data)
        {
            return CacheHelper.Get<List<string>, 数据库配置>(
                $"{Data.服务器名称}.{Data.用户名}.GetDatasources"
                , _GetDatasource, Data);

        }

        class ColumnInfo
        {
            public string ColumnName { get; set; }

            /// <summary>
            /// 数据类型
            /// </summary>
            public DbType DataType { get; set; }

            /// <summary>
            /// 数据长度
            /// </summary>
            public int Length { get; set; }

            /// <summary>
            /// 精度
            /// </summary>
            public int Scale { get; set; }

            public bool IsPrimaryKey { get; set; }

            public bool IsUniqueKey { get; set; }
            public bool IsIdentity { get; set; }
            public int IdentityStart { get; set; }
            public int IdentityIncrease { get; set; }


        }
        class TriggerInfo
        {
            public string Name { get; set; }

        }
        class TableInfo
        {
            public string TableName { get; set; }

            public List<ColumnInfo> Columns { get; } = new List<ColumnInfo>();

            //public List<>

        }

        static List<string> _GetDatasource(数据库配置 Data)
        {
            List<string> ret = null;
            SqlConnection con = null;
            try
            {
                con = new SqlConnection(Data.GetConnectionString());
                con.Open();
                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandText = @"SELECT name FROM [master].[dbo].[sysdatabases] ORDER BY dbid DESC";
                    using (var reader = cmd.ExecuteReader())
                    {
                        DataTable dt = DbHelper.DataReaderToDataTable(reader);
                        return dt.Rows.Cast<DataRow>().Select(x => x[0].ToString()).ToList();
                    }
                }
            }
            catch (Exception exp)
            {
                MainWindow.Current.ShowError(exp.Message);
            }
            finally
            {
                if (con != null)
                {
                    if (con.State == System.Data.ConnectionState.Open)
                        con.Close();
                    con.Dispose();
                }
            }
            return ret;
        }

        static List<string> _GetTabls(数据库配置 Data)
        {
            List<string> ret = null;
            SqlConnection con = null;
            try
            {
                if (Data.使用Windows验证)
                {
                    con = new SqlConnection($"Server={Data.服务器名称};Integrated Security=SSPI;Persist Security Info=False;");
                }
                else
                {
                    con = new SqlConnection($"Password={Data.密码};Persist Security Info=True;User ID={Data.用户名};Data Source={Data.服务器名称}");
                }
                con.Open();
                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandText = @"DECLARE @TAB NVARCHAR(200),@SQL NVARCHAR(MAX)

DECLARE _CUR_TAB CURSOR	FOR 
SELECT name from sys.[tables]

OPEN [_CUR_TAB]
WHILE(1=1)
BEGIN
    FETCH NEXT FROM [_CUR_TAB] INTO @TAB;
	IF(@@FETCH_STATUS!=0)BREAK;
	SET @SQL='SELECT
    ta.[name] AS TableName
	,[c].[name] AS [Code]	
	,CASE WHEN [t].[name]=''varchar'' OR t.[name]=''nvarchar'' OR t.[name]=''nchar'' THEN ''[''+t.[name]+''](''+CONVERT(NVARCHAR(200), c.[prec])+'')''
	WHEN t.[name]=''decimal'' THEN ''[''+t.[name]+''](''+CONVERT(NVARCHAR(200), c.[prec])+'',''+CONVERT(NVARCHAR(200),c.[scale])+'')''
	ELSE  ''[''+t.[name]+'']''
	END	AS 数据类型
	,CASE WHEN [c].[isnullable]=1 THEN ''NULL'' ELSE ''NOT NULL'' END AS [必填]
	
	,ISNULL( RIGHT(LEFT([e].[text],LEN(e.[text])-1),LEN(e.[text])-2),'''') AS 默认值,
    (SELECT
        [ex].[value]
     FROM
        [sys].[extended_properties] AS [ex]
     WHERE
        [ex].[major_id] = [c].[id] AND [ex].[minor_id] = [c].[colid]) AS [Title]
    ,[业务类型] = CASE WHEN EXISTS ( SELECT
                                    1
                                FROM
                                    [sys].[sysobjects] [s]
                                WHERE
                                    [s].[xtype] = ''PK'' AND [s].[parent_obj] = [c].[id] AND [s].[name] IN (
                                    SELECT
                                        [name]
                                    FROM
                                        [sys].[sysindexes]
                                    WHERE
                                        [indid] IN (
                                        SELECT
                                            [indid]
                                        FROM
                                            [sys].[sysindexkeys]
                                        WHERE
                                            [id] = [c].[id] AND [colid] = [c].[colid])) )
                  THEN ''主键''
                  ELSE ''''
             END
FROM
    [sys].[syscolumns] AS [c]
INNER JOIN [sys].[tables] AS [ta] ON [c].[id] = [ta].[object_id]
INNER JOIN (SELECT
                [name]
            ,   [system_type_id]
            FROM
                [sys].[types]
            WHERE
                [name] <> ''sysname'') AS [t] ON [c].[xtype] = [t].[system_type_id]
LEFT JOIN [sys].[syscomments] [e] ON [c].[cdefault] = [e].[id]
WHERE
    [ta].[name] = @Tab
ORDER BY
    [c].[colid];
	'
	
	EXEC SP_EXECUTESQL @SQL ,N'@Tab NVARCHAR(200)',@Tab
END
CLOSE [_CUR_TAB]
DEALLOCATE [_CUR_TAB]";
                    using (var reader = cmd.ExecuteReader())
                    {
                        DataSet ds = DbHelper.DataReaderToDataSet(reader);
                        
                    }
                }
            }
            catch (Exception exp)
            {
                MainWindow.Current.ShowError(exp.Message);
            }
            finally
            {
                if (con != null)
                {
                    if (con.State == System.Data.ConnectionState.Open)
                        con.Close();
                    con.Dispose();
                }
            }
            return ret;
        }

        public static List<表映射> InitMapping(this MyDbConfig cfg)
        {
            List<string> source = cfg.数据源.GetTables();
            List<string> target = cfg.目标数据库.GetTables();
            cfg.表映射.AddRange(source.Select(x => new 表映射()
            {
                源表名 = x
                ,
                目标表名 = target.FirstOrDefault(y => y == x)
            }));
            throw new NotImplementedException();
        }
    }
}
